USE onExam;
GO

IF OBJECT_ID('uspBatchDelCenter', 'P') IS NOT NULL 
    DROP PROC dbo.uspBatchDelCenter;
GO

CREATE PROCEDURE uspBatchDelCenter
    (
      @Center AS CenterTableType READONLY
    )
/*********************
Proc:uspBatchDelCenter
Use:批量删除指挥中心
Creator:Mark Yao
Create Date:2012-04-22
INPUT:

OUTPUT:

Tables:
1.dbo.Center  所属指挥中心

Returns:
-1:指挥中心已经使用，不允许删除！
0：删除失败
1：删除成功
e.g
DECLARE @Center AS CenterTableType;
	INSERT INTO @Center(CenterId)
	VALUES (29),(30),(31);
EXEC uspBatchDelCenter @Center;
GO	
 
**********************/
AS 
    BEGIN
        SET NOCOUNT ON;
        DECLARE @flag INT;
  
        BEGIN TRAN   
        -- 创建临时表
        SELECT  CenterId
        INTO    #tmp
        FROM    @Center;
        
    -- 判断是否已经使用
        IF EXISTS ( SELECT  b.CenterId
                    FROM    #tmp a
                            LEFT JOIN Customer b ON a.CenterId = b.CenterId
                    WHERE   b.CenterId IS NOT NULL ) 
            SET @flag = -1;
        ELSE 
            BEGIN
                DELETE  dbo.Center
                FROM    #tmp
                WHERE   #tmp.CenterId = Center.CenterId;
                SELECT  @flag = 1;   	
                
                -- 删除临时表
                DROP TABLE #tmp;
            END
            
  
        IF @@ERROR <> 0 
            BEGIN
                ROLLBACK;
                SET @flag = 0;
            END    
        ELSE 
            COMMIT;
  
  -- 返回结果
        SELECT  @flag Flag;
        SET NOCOUNT OFF;
    END

GO

